﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.MarketModel
{
    public partial class MBaoYangList : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "市场_保养件赠送管控表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "保养件赠送";
            }

        }
        protected string SQSJFrom
        {
            get
            {
                DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                return GetRequestValue<string>("DateFrom", dt.ToString("yyyy-MM-dd"));

            }
        }
        protected string SQSJTo
        {
            get
            {

                return GetRequestValue<string>("DateTo", DateTime.Now.ToString("yyyy-MM-dd"));

            }
        }
        /// <summary>
        /// 排序字段 值为 id
        /// </summary>
        protected override string orderField
        {
            get
            {
                return string.IsNullOrEmpty(base.orderField) ? "id" : base.orderField;
            }
        }
        /// <summary>
        ///  排序方式 desc或 asc
        /// </summary>
        protected override string orderDirection
        {
            get
            {
                return string.IsNullOrEmpty(base.orderDirection) ? "DESC" : base.orderDirection;
            }
        }


        protected override string SQLSort
        {
            get
            {
                return (orderField + " " + orderDirection);
            }
        }
        protected string GetSQLWhere()
        {
            string wheresql = "";
            string sqlcommonFiled = ""; 

            if (!string.IsNullOrEmpty(SQSJFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator(" SQSJ ", QueryOperatorsEnum.范围, SQSJFrom + "|" + SQSJTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            return wheresql;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            //初始化
            InitializeWorkbook();
            ISheet sheet1 = hssfworkbook.GetSheet(SheetName);
            int TotalCount = 0;
            DataTable exportDT = MBaoYangController.GetPagination(GetSQLWhere(), 1, 999999, SQLSort, ref TotalCount);
            int rowindexBase = 1;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(rowReal);//
                row1.CreateCell(1).SetCellValue(dr["SQR"].ToString());//
                row1.CreateCell(2).SetCellValue(dr["JXS"].ToString());
                row1.CreateCell(3).SetCellValue(dr["YHM"].ToString());
                row1.CreateCell(4).SetCellValue(dr["JX"].ToString());
                row1.CreateCell(5).SetCellValue(dr["JH"].ToString());
                row1.CreateCell(6).SetCellValue(dr["ZSYY"].ToString());
                row1.CreateCell(7).SetCellValue(dr["BB_ZHHTH"].ToString());
                row1.CreateCell(8).SetCellValue(dr["PJZL"].ToString());
                row1.CreateCell(9).SetCellValue(dr["BZ"].ToString());
                row1.CreateCell(10).SetCellValue(dr["SL"].ToString());
                row1.CreateCell(11).SetCellValue(dr["SQSJ"].ToString());
                row1.CreateCell(12).SetCellValue(dr["ZXSJ"].ToString());
                row1.CreateCell(13).SetCellValue(dr["CKSJ"].ToString());
                row1.CreateCell(14).SetCellValue(dr["CKDH"].ToString());
                row1.CreateCell(15).SetCellValue(dr["PZH"].ToString());

                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

           ;

            //Force excel to recalculate all the formula while open
            sheet1.ForceFormulaRecalculation = true;

            //写入
            WriteToFile();
        }
        private Double GetDBDouble(object v)
        {
            if (v is DBNull)
                return 0;
            return Convert.ToDouble(v);
        }
        private string GetDBString(object v)
        {
            if (v is DBNull)
                return "";
            return v.ToString();
        }

        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}到{2}.xls", "保养件赠送管控表",SQSJFrom,SQSJTo).Trim();
        }
    }
}